﻿CREATE PROCEDURE [dbo].[usp_select_AlertSubscription_Count_Over_Time_By_Application_ServiceName]
(
        @Application       varchar(256)      = NULL
	   ,@StartDate         datetime         = NULL
	   ,@EndDate           datetime         = NULL
	   ,@Debug             bit              = 0
)
AS

SET NOCOUNT ON;

-- Bootstrap
DECLARE @DebugPrefix       varchar(10);
DECLARE @ErrorCode         int;
DECLARE @dateDiff int;

SET @ErrorCode          = 0;
SET @DebugPrefix        = '>>> DEBUG: ';

-- Print parameters
IF (@Debug > 0)
BEGIN
    PRINT '@Application = ' + @Application
	PRINT '@StartDate = ' + CAST (@StartDate as varchar)
	PRINT '@EndDate = ' + CAST (@EndDate as varchar)
END;



SET @dateDiff = DATEDIFF ( day  , @StartDate , @EndDate );
if  @dateDiff >31 -- get group by month
begin
	SELECT
		alerthistory.[Application], alerthistory.ServiceName ServiceName, CAST(FLOOR(Cast(alerthistory.[inserteddate] as float)) as DateTime) DT, Count(*) as Total
	from

	alertsubscriptionhistory 
	inner join alerthistory on alertsubscriptionhistory.alerthistoryid=alerthistory.alerthistoryid
	
	where alerthistory.[Application] = @Application and alerthistory.[inserteddate] between @StartDate and @EndDate

	GROUP BY alerthistory.[Application],alerthistory.ServiceName, CAST(FLOOR(Cast(alerthistory.[inserteddate]  as float)) as DateTime)
	ORDER BY alerthistory.[Application],alerthistory.ServiceName, CAST(FLOOR(Cast(alerthistory.[inserteddate]  as float)) as DateTime)
end
else
begin
	SELECT  alerthistory.[Application],alerthistory.ServiceName,alerthistory.[inserteddate] DT, 1 as Total  
	from alertsubscriptionhistory 
	inner join alerthistory on alertsubscriptionhistory.alerthistoryid=alerthistory.alerthistoryid
	
	WHERE alerthistory.[inserteddate] BETWEEN @StartDate AND @EndDate 
	AND alerthistory.[Application]= @Application
	ORDER BY 	alerthistory.[Application],alerthistory.ServiceName,alerthistory.[inserteddate]
end 

-- CHECK FOR ERROR
SET @ErrorCode = @@Error;
IF (@ErrorCode <> 0) GOTO ERROR_HANDLER;
			
RETURN 0;

-- Handles errors.
ERROR_HANDLER:
IF (@ErrorCode <> 0)
BEGIN
    IF (@Debug > 0) 
		BEGIN
			PRINT @DebugPrefix + 'Error Code = ' + CAST(@ErrorCode AS varchar(10)) + ' occurred.';
			RETURN @ErrorCode
		END

    -- Don't return any evidence of what specific error occurred, i.e. 
    -- we want to ward off potential hackers.
    RETURN 1;
END;

SET NOCOUNT OFF;
